package com.example.base.jdbc;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.base.jdbc.model.RowMap;
import com.example.base.utils.SpringContextUtils;

import javax.sql.DataSource;
import java.awt.*;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBSql {

    public static int insertMap(String tableName, Map<String,Object> maps){
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("INSERT INTO ").append(tableName).append("(");
        List<Object> objects = new ArrayList<>();
        for (String column : maps.keySet()) {
            sqlBuilder.append(column).append(",");
            objects.add(maps.get(column));
        }
        //删除最后一个字符
        sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
        sqlBuilder.append(") VALUES (");
        for (int i = 0; i < maps.size(); i++) {
            sqlBuilder.append("?,");
        }
        //删除最后一个字符
        sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
        sqlBuilder.append(")");
        int index =executeInsert(sqlBuilder.toString(),objects.toArray());
        return index;
    }

    public static int updateMap(String tableName,String primaryKey,Map<String,Object> map){
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("UPDATE ").append(tableName).append(" SET ");
        List<Object> objects=new ArrayList<Object>();
        for (String column : map.keySet()) {
            if (!column.equalsIgnoreCase(primaryKey)) {
                sqlBuilder.append(column).append("=?,");
                objects.add(map.get(column));
            }
        }
        sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
        sqlBuilder.append(" WHERE ").append(primaryKey).append(" =? ");
        objects.add(map.get(primaryKey));
        return executeUpdate(sqlBuilder.toString(),objects.toArray());
    }



    public static int executeUpdate(String sql,Object ... args){
        System.out.println("update "+sql);
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            // 连接数据库
            connection = getConnection();
            // 创建 PreparedStatement 对象，并设置参数
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                int index=i+1;
                statement.setObject(index, args[i]);
            }
            // 执行更新操作
            int num=statement.executeUpdate();
            return num;
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                // 关闭资源
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }


    public static boolean execute(String sql){
        System.out.println("execute "+sql);
        Connection connection = null;
        Statement statement = null;
        try {
            // 连接数据库
            connection = getConnection();
            statement = connection.createStatement();
            // 执行操作
            int code= statement.executeUpdate(sql);
            return code>0;
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                // 关闭资源
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }




    /**
     * 执行新增并返回主键
     * @param sql  sql
     * @param args 参数
     * @return     主键
     * @throws SQLException
     */
    public static int executeInsert(String sql,Object ... args)  {
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        System.out.println(sql);
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            int num=statement.executeUpdate();
            resultSet = statement.getGeneratedKeys();
            if (resultSet.next()) {
                return resultSet.getInt(1); // 返回生成的主键 ID
            } else {
                if(num>0){
                    return num;
                }
                return -1; // 插入失败，返回 -1
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                if(connection != null){
                    connection.close();
                }
                if(statement != null){
                    statement.close();
                }
                if(resultSet != null){
                    resultSet.close();
                }
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return -1;
    }


    /**
     * 获取到查询的参数
     * @param sql  sql
     * @param params 参数
     * @return  查询的数据
     * @throws SQLException
     * @throws IOException
     */
    public static List<Map<String,Object>> getMaps(String sql, Object... params) throws SQLException, IOException {
        System.out.println("执行sql:"+sql+" 执行参数:"+ JSONObject.toJSONString(params));
        Connection connection=null;
        try {
            connection=getConnection();
            ResultSet resultSet=executeQuery(connection,sql,params);
            List<Map<String,Object>> maps=resultSetToMaps(resultSet);
            return maps;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static Map<String,Object> getMap(String sql, Object... params) throws SQLException, IOException {
        Connection connection=null;
        try {
            connection=getConnection();
            ResultSet resultSet=executeQuery(connection,sql,params);
            Map<String,Object> map=resultSetToMap(resultSet);
            return map;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static RowMap getRowMap(String sql, Object... params) throws SQLException, IOException {
        Connection connection=null;
        try {
            connection=getConnection();
            ResultSet resultSet=executeQuery(connection,sql,params);
            RowMap map=resultSetToRowMap(resultSet);
            return map;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static JSONObject getJSONObject(String sql, Object... params) throws SQLException, IOException {
        Connection connection=null;
        try {
            connection=getConnection();
            ResultSet resultSet=executeQuery(connection,sql,params);
            JSONObject obj=resultSetToJSONObject(resultSet);
            return obj;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }


    public static JSONArray getJSONOArray(String sql, Object... params) throws SQLException, IOException {
        Connection connection=null;
        try {
            connection=getConnection();
            ResultSet resultSet=executeQuery(connection,sql,params);
            JSONArray array=resultSetToJSONArray(resultSet);
            return array;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }


    public static <T> T getObj(String sql,Class<T> cls, Object... params) throws SQLException, IOException {
        Connection connection=null;
        try {
            connection=getConnection();
            ResultSet resultSet=executeQuery(connection,sql,params);
            T t=resultSetToClassObj(resultSet,cls);
            return t;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static <T> List<T> getObjs(String sql,Class<T> cls, Object... params){
        Connection connection=null;
        try {
            connection=getConnection();
            ResultSet resultSet=executeQuery(connection,sql,params);
            List<T> ts=resultSetToClassObjs(resultSet,cls);
            return ts;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }



    public static List<RowMap> getRowMaps(String sql, Object... params){
        System.out.println("执行sql:"+sql+" 执行参数:"+ JSONObject.toJSONString(params));
        Connection connection=null;
        try {
            connection=getConnection();
            ResultSet resultSet=executeQuery(connection,sql,params);
            List<RowMap> maps=resultSetToRowMaps(resultSet);
            return maps;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }


    public static Double getDouble(String sql,Object...params) throws SQLException {
        Connection connection=null;
        ResultSet resultSet=null;
        try {
            connection=getConnection();
            resultSet=executeQuery(connection,sql, params);
            return resultSetToDouble(resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection!=null){
                    try {
                        connection.close();
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }


    public static Float getFloat(String sql,Object...params) throws SQLException {
        Connection connection=null;
        ResultSet resultSet=null;
        try {
            connection=getConnection();
            resultSet=executeQuery(connection,sql, params);
            return resultSetToFloat(resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection!=null){
                    try {
                        connection.close();
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }


    public static Boolean getBoolean(String sql,Object...params) throws SQLException {
        Connection connection=null;
        ResultSet resultSet=null;
        try {
            connection=getConnection();
            resultSet=executeQuery(connection,sql, params);
            return resultSetToBoolean(resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection!=null){
                    try {
                        connection.close();
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }




    public static Integer getInteger(String sql,Object...params) throws SQLException {
        Connection connection=null;
        ResultSet resultSet=null;
        try {
            connection=getConnection();
            resultSet=executeQuery(connection,sql, params);
            return resultSetToInt(resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection!=null){
                    try {
                        connection.close();
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }

    public static String getString(String sql,Object ... params){
        Connection connection=null;
        ResultSet resultSet=null;
        try {
            connection=getConnection();
            resultSet=executeQuery(connection,sql, params);
            return resultSetToString(resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection!=null){
                    try {
                        connection.close();
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }

    private static ResultSet executeQuery(Connection connection,String sql, Object... params) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }
        return statement.executeQuery();
    }




    private static Connection getConnection(){
        Connection connection = null;
        try {
            DataSource dataSource=(DataSource) SpringContextUtils.getBean("appDataSource");
            connection=dataSource.getConnection();
            return connection;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

//    private static Connection getConnection(DataSource dataSource){
//        try {
//            Class.forName(dataSource.getClassDriver());
//            Connection connection = DriverManager.getConnection(dataSource.getUrl(),dataSource.getUserName(),dataSource.getPassWord());
//            return connection;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }
//        return null;
//    }

    private static List<Map<String,Object>> resultSetToMaps(ResultSet rs) throws SQLException, IOException {
        List<Map<String,Object>> maps=new ArrayList<>();
        while (rs.next()) {
            JSONObject obj=new JSONObject();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                obj.put(columnName,value);
            }
            maps.add(obj);
        }
        return maps;
    }

    private static List<RowMap> resultSetToRowMaps(ResultSet rs) throws SQLException {
        List<RowMap> rowMapList=new ArrayList<>();
        while (rs.next()) {
            RowMap rowMap=new RowMap();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                rowMap.put(columnName,value);
            }
            rowMapList.add(rowMap);
        }
        return rowMapList;
    }


    private static <T> List<T> resultSetToClassObjs(ResultSet rs,Class<T> cls) throws SQLException {
        List<T> tList=new ArrayList<>();
        JSONObject object=null;
        while (rs.next()) {
            object=new JSONObject();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                object.put(columnName,value);
            }
            T t=JSONObject.parseObject(object.toJSONString(),cls);
            tList.add(t);
        }
        return tList;
    }

    private static <T> T resultSetToClassObj(ResultSet rs,Class<T> cls) throws SQLException {
        JSONObject jsonObject=new JSONObject();
        while (rs.next()) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                jsonObject.put(columnName,value);
            }
            return JSONObject.parseObject(jsonObject.toJSONString(),cls);
        }
        return JSONObject.parseObject(jsonObject.toJSONString(),cls);
    }

    private static RowMap resultSetToRowMap(ResultSet rs) throws SQLException {
        RowMap rowMap=new RowMap();
        while (rs.next()) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                rowMap.put(columnName,value);
            }
            return rowMap;
        }
        return rowMap;
    }


    private static JSONObject resultSetToJSONObject(ResultSet rs) throws SQLException {
        JSONObject jsonObject=new JSONObject();
        while (rs.next()) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                jsonObject.put(columnName,value);
            }
            return jsonObject;
        }
        return jsonObject;
    }


    private static JSONArray resultSetToJSONArray(ResultSet rs) throws SQLException {
        JSONArray array=new JSONArray();
        JSONObject jsonObject=null;
        while (rs.next()) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                jsonObject.put(columnName,value);
            }
            array.add(jsonObject);
        }
        return array;
    }

    private static Map<String,Object> resultSetToMap(ResultSet rs) throws SQLException {
        Map<String,Object> map=new HashMap<>();
        while (rs.next()) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                map.put(columnName,value);
            }
            return map;
        }
        return map;
    }

    private static int resultSetToInt(ResultSet rs) throws SQLException {
        while (rs.next()){
            return rs.getInt(1);
        }
        return 0;
    }

    private static String resultSetToString(ResultSet rs) throws SQLException {
        while (rs.next()){
            return rs.getString(1);
        }
        return null;
    }

    private static Double resultSetToDouble(ResultSet rs) throws SQLException {
        while (rs.next()){
            return rs.getDouble(1);
        }
        return null;
    }

    private static Boolean resultSetToBoolean(ResultSet rs) throws SQLException {
        while (rs.next()){
            return rs.getBoolean(1);
        }
        return null;
    }

    private static Float resultSetToFloat(ResultSet rs) throws SQLException {
        while (rs.next()){
            return rs.getFloat(1);
        }
        return null;
    }

    //开启事务
    public static void transaction(){
        Connection connection = null;
        try {
            // 1. 获取数据库连接
            String url = "jdbc:mysql://localhost:3306/mydatabase";
            String username = "root";
            String password = "password";
            connection = DriverManager.getConnection(url, username, password);

            // 2. 设置自动提交模式为 false
            connection.setAutoCommit(false);

            // 3. 执行 SQL 语句
            Statement statement = connection.createStatement();

            statement.executeUpdate("INSERT INTO mytable (column1, column2) VALUES ('value1', 'value2')");
            statement.executeUpdate("UPDATE mytable SET column1 = 'new value' WHERE column2 = 'value2'");

            // 4. 提交事务
            connection.commit();
        } catch (SQLException e) {
            // 发生异常，回滚事务
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 5. 恢复自动提交模式
            if (connection != null) {
                try {
                    connection.setAutoCommit(true);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            // 关闭数据库连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void batchInsert() throws SQLException {
        Connection conn = getConnection();

        // 创建一个 PreparedStatement 对象
        PreparedStatement ps = conn.prepareStatement("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");

        // 设置要批量新增的数据
        ps.setString(1, "John");
        ps.setString(2, "john@example.com");
        ps.setInt(3, 25);
        ps.addBatch();

        ps.setString(1, "Jane");
        ps.setString(2, "jane@example.com");
        ps.setInt(3, 30);
        ps.addBatch();

        ps.setString(1, "Jim");
        ps.setString(2, "jim@example.com");
        ps.setInt(3, 35);
        ps.addBatch();

        // 执行批量新增操作
        int[] rowsAffected = ps.executeBatch();
        System.out.println("Rows affected: " + rowsAffected.length);
    }


}
